IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SecurityQuestions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SecurityQuestions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Question] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_SecurityQuestions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM information_schema.COLUMNS WHERE table_name='Users' AND column_name='LastLoginTime')
ALTER TABLE  Users ADD [LastLoginTime] datetime NULL
GO

IF NOT EXISTS(select name from syscolumns where id=object_id('Users') and name='IsResetPasswordNeeded')
ALTER TABLE [dbo].[Users]
    ADD [IsResetPasswordNeeded] BIT CONSTRAINT [DF_Users_IsResetPasswordNeeded] DEFAULT ((1)) NOT NULL;


GO

IF NOT EXISTS(select name from syscolumns where id=object_id('Users') and name='PasswordChangedOn')
ALTER TABLE [dbo].[Users]
    ADD [PasswordChangedOn] DATETIME CONSTRAINT [DF_Users_PasswordChangedOn] DEFAULT (getdate()) NOT NULL;


GO

IF NOT EXISTS (SELECT * FROM information_schema.COLUMNS WHERE table_name='Users' AND column_name='SecurityQuestionID')
ALTER TABLE  Users ADD [SecurityQuestionID] int NULL
GO

IF NOT EXISTS (SELECT * FROM information_schema.COLUMNS WHERE table_name='Users' AND column_name='SecurityAnswer')
ALTER TABLE  Users ADD [SecurityAnswer] nvarchar(500) NULL
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Users_SecurityQuestions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))
ALTER TABLE [dbo].[Users]  WITH CHECK ADD CONSTRAINT [FK_Users_SecurityQuestions] FOREIGN KEY([SecurityQuestionID])
REFERENCES [dbo].[SecurityQuestions] ([ID])
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChangedPasswords]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ChangedPasswords](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Password] [nvarchar](500) NOT NULL,
	[PasswordChangedOn] [datetime] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_ChangedPasswords] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ChangedPasswords_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[ChangedPasswords]'))
ALTER TABLE [dbo].[ChangedPasswords]  WITH CHECK ADD  CONSTRAINT [FK_ChangedPasswords_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
ON DELETE CASCADE
GO

SET IDENTITY_INSERT [dbo].[SecurityQuestions] ON
IF NOT EXISTS (SELECT * FROM [dbo].[SecurityQuestions] WHERE Question = 'What is your nick name?')
INSERT [dbo].[SecurityQuestions] ([ID],[Question]) VALUES (1, N'What is your nick name?')
IF NOT EXISTS (SELECT * FROM [dbo].[SecurityQuestions] WHERE Question = 'What color do you like most?')
INSERT [dbo].[SecurityQuestions] ([ID],[Question]) VALUES (2, N'What color do you like most?')
IF NOT EXISTS (SELECT * FROM [dbo].[SecurityQuestions] WHERE Question = 'What is your favorite sports?')
INSERT [dbo].[SecurityQuestions] ([ID],[Question]) VALUES (3, N'What is your favorite sports?')
IF NOT EXISTS (SELECT * FROM [dbo].[SecurityQuestions] WHERE Question = 'What is your home town?')
INSERT [dbo].[SecurityQuestions] ([ID],[Question]) VALUES (4, N'What is your home town?')
IF NOT EXISTS (SELECT * FROM [dbo].[SecurityQuestions] WHERE Question = 'Who is your favorite author?')
INSERT [dbo].[SecurityQuestions] ([ID],[Question]) VALUES (5, N'Who is your favorite author?')
SET IDENTITY_INSERT [dbo].[SecurityQuestions] OFF
GO